package com.tianwu.databasedesign;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.CharArrayBuffer;
import android.database.ContentObserver;
import android.database.Cursor;
import android.database.DataSetObserver;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.os.Bundle;
import android.util.Log;

import java.util.ArrayList;
import java.util.Date;

/**
 * Created by Tianwu on 2016/6/28.
 */
public class Sql {

    public static ArrayList<Equipment> getAllEquipment(Context context){

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        ArrayList<Equipment> equipments = new ArrayList<>();

        String[] projection = {"*"};

        Cursor cursor = db.query(
                DataBase.TABLE_EQU_NAME,                       // The table to query
                projection,                                     // The columns to return
                null,                                           // The columns for the WHERE clause
                null,                                           // The values for the WHERE clause
                null,                                           // don't group the rows
                null,                                           // don't filter by row groups
                null                                            // The sort order
            );
        int count = cursor.getCount();
        if(count <= 0) return null;
        cursor.moveToFirst();

        for(int i = 0; i < count; i++){
            equipments.add(new Equipment(
                    cursor.getString(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4),
                    cursor.getString(5),
                    cursor.getFloat(6)
            ));
            cursor.moveToNext();
        }
        return equipments;
    }
    public static ArrayList<Record> getAllRecord(Context context,String id){

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        ArrayList<Record> records = new ArrayList<>();

        String[] projection = {"*"};
        String selection = Record.ID + " = " + "'" + id + "'" ;

        Cursor cursor = db.query(
                DataBase.TABLE_RECORD_NAME,                       // The table to query
                projection,                                     // The columns to return
                selection,                                           // The columns for the WHERE clause
                null,                                           // The values for the WHERE clause
                null,                                           // don't group the rows
                null,                                           // don't filter by row groups
                null                                            // The sort order
        );
        int count = cursor.getCount();
        if(count <= 0) return null;
        cursor.moveToFirst();

        for(int i = 0; i < count; i++){
            records.add(new Record(
                    cursor.getString(0),
                    cursor.getString(1),
                    cursor.getFloat(2),
                    cursor.getString(3),
                    cursor.getString(4)
            ));
            cursor.moveToNext();
        }

        return records;
    }
    public static ArrayList<Lab> getAllLab(Context context){

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        ArrayList<Lab> labs = new ArrayList<>();

        String[] projection = {"*"};

        Cursor cursor = db.query(
                DataBase.TABLE_LAB_NAME,                       // The table to query
                projection,                                     // The columns to return
                null,                                           // The columns for the WHERE clause
                null,                                           // The values for the WHERE clause
                null,                                           // don't group the rows
                null,                                           // don't filter by row groups
                null                                            // The sort order
        );
        int count = cursor.getCount();
        if(count <= 0) return null;
        cursor.moveToFirst();

        for(int i = 0; i < count; i++){
            labs.add(new Lab(
                    cursor.getString(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3)
            ));
            cursor.moveToNext();
        }
        return labs;
    }

    public static ArrayList<Equipment> query(Context context,String id,String name,String room){//example for query with sql.

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        ArrayList<Equipment> result = new ArrayList<>();

        String select = "SELECT equipments.id,equipments.name,kind,chamber,date,factory,expenditure ";
        String from =  "FROM equipments,lab ";
        String where = "WHERE equipments.chamber = lab.id and ";
        if(id.length() > 0)
            where += "equipments.id = " + "'" + id + "' ";
        if(name.length() > 0) {
            if (id.length() > 0)
                where += " and ";
            where += "equipments.name = " + "'" + name + "' ";
        }
        if(room.length() > 0) {
            if (id.length() > 0 || name.length() > 0)
                where += " and ";
            where += "lab.name = " + "'" + room + "'";
        }

        String sql =
                        select +
                        from +
                        where;

        Log.d("sql",sql);

        Cursor cursor = db.rawQuery(sql,null);

        int count = cursor.getCount();
        Log.d("conut",Integer.toString(count));
        if(count > 0){
            cursor.moveToFirst();
            for(int i = 0;i < count;i++){
                result.add(new Equipment(
                        cursor.getString(0),
                        cursor.getString(1),
                        cursor.getString(2),
                        cursor.getString(3),
                        cursor.getString(4),
                        cursor.getString(5),
                        cursor.getFloat(6)
                ));
                cursor.moveToNext();
            }
        }
        return result;
    }

    public static Lab getLabById(Context context,String id){
        SQLiteDatabase db = new DataBase(context).getReadableDatabase();
        String[] projection = {"*"};
        String selection = Lab.ID + " = " + "'" + id + "'" ;
        Cursor cursor = db.query(
                DataBase.TABLE_LAB_NAME,
                projection,
                selection,
                null,
                null,
                null,
                null
        );
        if(cursor.getCount() <= 0)
            return null;
        cursor.moveToFirst();
        return  new Lab(
                cursor.getString(0),
                cursor.getString(1),
                cursor.getString(2),
                cursor.getString(3)
        );
    }

    public static Equipment getEquipmentById(Context context, String id){

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        String[] projection = {"*"};
        String selection = Equipment.ID + " = " + "'" + id + "'" ;
        Cursor cursor = db.query(
                DataBase.TABLE_EQU_NAME,
                projection,
                selection,
                null,
                null,
                null,
                null
        );
        if(cursor.getCount() <= 0)
            return null;
        cursor.moveToFirst();
        return new Equipment(
                cursor.getString(0),
                cursor.getString(1),
                cursor.getString(2),
                cursor.getString(3),
                cursor.getString(4),
                cursor.getString(5),
                cursor.getFloat(6)
            );
    }
    public static boolean deleteEquipmentById(Context context, String id){

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        String selection = Equipment.ID + " = " + "'" + id + "'" ;
        return db.delete(
                DataBase.TABLE_EQU_NAME,
                selection,
                null
        ) > 0;
    }

    public static boolean  addEquipment(Context context, Equipment e){

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        ContentValues values = new ContentValues();
        values.put(Equipment.ID,e.id);
        values.put(Equipment.NAME,e.name);
        values.put(Equipment.KIND,e.kind);
        values.put(Equipment.CHAMBER,e.chamber);
        values.put(Equipment.DATE,e.date.toString());
        values.put(Equipment.FACTORY, e.factory);
        values.put(Equipment.EXPENDITURE, e.expenditure);

        long newRowId = db.insert(DataBase.TABLE_EQU_NAME, null, values);

        return newRowId > 0;
    }
    public static boolean addRoom(Context context, Lab lab){
        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        ContentValues values = new ContentValues();
        values.put(Lab.ID,lab.id);
        values.put(Lab.NAME,lab.name);
        values.put(Lab.FUNCTIONARY,lab.functionary);
        values.put(Lab.LOCATION,lab.location);

        long newRowId = db.insert(DataBase.TABLE_LAB_NAME, null, values);

        return newRowId > 0;
    }
    public static boolean addRecord(Context context, Record record){

        SQLiteDatabase db = new DataBase(context).getReadableDatabase();

        ContentValues values = new ContentValues();
        values.put(Record.ID,record.id);
        values.put(Record.DATE,record.date);
        values.put(Record.COST,record.cost);
        values.put(Record.PROJECT,record.project);
        values.put(Record.OTHERS,record.others);

        long newRowId = db.insert(DataBase.TABLE_RECORD_NAME, null, values);

        return newRowId > 0;
    }
    public static String getEqNameById(Context context,String id){
        SQLiteDatabase db = new DataBase(context).getReadableDatabase();
        String[] projection = {Equipment.NAME};
        String selection = Equipment.ID + " = " + "'" + id + "'" ;
        Cursor cursor = db.query(
                DataBase.TABLE_EQU_NAME,
                projection,
                selection,
                null,
                null,
                null,
                null
        );
        cursor.moveToFirst();
        return  cursor.getString(0);
    }

}
